package com.gzq.my.listenner;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.gzq.domain.TestUser;
import com.gzq.mapper.TestUserMapper;
import org.springframework.beans.factory.annotation.Autowired;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * 每读完一个sheet就会调用两个方法
 */
public class EasyExcelListenner extends AnalysisEventListener<TestUser> {

    @Autowired
    TestUserMapper testUserMapper;
    //每读取到该数目进行批量添加操作
    private static final int BATCH_COUNT = 10*10000;
    List<TestUser> list=new ArrayList<>();
    @Override
    public void invoke(TestUser testUser, AnalysisContext analysisContext) {
        list.add(testUser);

        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }


    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        list.clear();
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        try {
            //sql语句
            String sql = "insert into test_user(dept_id,login_name,username,email,phone,sex,create_date)values(?,?,?,?,?,?,?)";
            Connection con = null;
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取到远程服务器的连接
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/ry-cloud?useUnicode=true&"+
                            "characterEncoding=utf-8&useSSL=false&rewriteBatchedStatements=true",
                    "root","123456");
            //设置非自动提交事务
            con.setAutoCommit(false);

            PreparedStatement pstat = con.prepareStatement(sql);
            long start = System.currentTimeMillis();
            for (int i=0; i<list.size(); i++) {

                TestUser user = list.get(i);
                pstat.setString(1, user.getDeptId()+"");
                pstat.setString(2, user.getLoginName());
                pstat.setString(3, user.getUsername());
                pstat.setString(4, user.getEmail());
                pstat.setString(5, user.getPhone());
                pstat.setString(6, user.getSex()+"");
                SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String strDate1 = sdf1.format(user.getCreateDate());
                pstat.setString(7, strDate1);


                //10w提交一次
                pstat.addBatch();
                if(i % 100000 == 0){
                    pstat.executeBatch();
                    pstat.clearBatch();
                }
            }
            pstat.executeBatch(); //执行批处理
            pstat.clearBatch();  //清空批处理
            con.commit();
            long end = System.currentTimeMillis();

            pstat.close();
            con.close();
            System.out.print((end-start)+"豪秒。");

        }catch (Exception e){
            e.printStackTrace();
        }

        System.out.println("存储数据库成功");
    }
}
